package com.china08.yxyapi.service.db.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.china08.yxyapi.service.db.UserAuthService;

@Service
public class UserAuthServiceImpl implements UserAuthService {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public Integer countByUserIdAndStatus(String userId, Integer status) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select                                                  ");
        sb.append("   count(t1.id)                                          ");
        sb.append(" from yxy_lessons.yxy_lessons_school_lesson_auth t1      ");
        sb.append("   left join yxy_lessons.yxy_lessons_school_lesson t2    ");
        sb.append("     on (t1.lesson_id = t2.id)                           ");
        sb.append(" where t1.user_id = ?                                    ");
        sb.append("     and t1.status = ?                                   ");
        sb.append("     and t2.id is not null                               ");
        return jdbcTemplate.queryForObject(sb.toString(), new Object[] { userId, status }, Integer.class);
    }

    @Override
    public Map<String, Object> userAuthKeChengList(String userId, String catalogId1, String catalogId2, String keyword, Integer pageNum, Integer status) {
        List<Object> paramList = new ArrayList<Object>();

        StringBuffer sBuffer = new StringBuffer();
        sBuffer.append("  select                                                        ");
        sBuffer.append("    t1.user_id,                                                 ");
        sBuffer.append("    t1.lesson_id,                                               ");
        sBuffer.append("    t2.name,                                                    ");
        sBuffer.append("    t2.catalog_id1,                                             ");
        sBuffer.append("    t2.catalog_id2,                                             ");
        sBuffer.append("    t2.count,                                                   ");
        sBuffer.append("    t2.author,                                                  ");
        sBuffer.append("    t2.created_date                                             ");
        sBuffer.append("  from yxy_lessons.yxy_lessons_school_lesson_auth t1            ");
        sBuffer.append("    left join yxy_lessons.yxy_lessons_school_lesson t2          ");
        sBuffer.append("      on (t1.lesson_id = t2.id)                                 ");
        sBuffer.append("  where t1.user_id = ?                                          ");
        sBuffer.append("     and t2.status = 2                                          ");

        paramList.add(userId);

        if (StringUtils.isNotBlank(catalogId1)) {
            sBuffer.append(" and t2.catalog_id1 = ? ");
            paramList.add(catalogId1);
        }
        if (StringUtils.isNotBlank(catalogId2)) {
            sBuffer.append(" and t2.catalog_id2 = ? ");
            paramList.add(catalogId2);
        }
        if (StringUtils.isNotBlank(keyword)) {
            sBuffer.append(" and t2.name like ? ");
            paramList.add(keyword);
        }

        List<Map<String, Object>> list = jdbcTemplate.queryForList(sBuffer.toString(), paramList.toArray());
        Integer allPage = list.size();
        sBuffer.append("     order by t2.last_modified_date desc                ");
        sBuffer.append("     limit ?,?                                          ");
        paramList.add((pageNum - 1) * 10);
        paramList.add(10);
        list = jdbcTemplate.queryForList(sBuffer.toString(), paramList.toArray());
        Map<String, Object> map = new HashMap<>();
        map.put("allPage", allPage);
        map.put("list", list);
        return map;
    }

    @Override
    public Map<String, Object> userAuthAllKeChengList(List<String> lists, String schoolId, String catalogId1, String catalogId2, String keyword, Integer pageNum) {

        List<Object> paramList = new ArrayList<>();

        StringBuffer sb1 = new StringBuffer();
        sb1.append(" select                                             ");
        sb1.append("   count(t1.id)                                     ");
        sb1.append(" from yxy_lessons.yxy_lessons_school_lesson t1      ");
        sb1.append("   left join yxy_lessons.yxy_lessons_school_catalog t2          ");
        sb1.append("     on (t1.catalog_id1 = t2.id)                    ");
        sb1.append("   left join yxy_lessons.yxy_lessons_school_catalog t3          ");
        sb1.append("     on (t1.catalog_id2 = t3.id)                    ");
        sb1.append(" where t1.school_id = ?                             ");
        sb1.append("      and t1.status = ?                             ");
        sb1.append("      and t1.is_visible = ?                         ");
        sb1.append("      and t1.vip = ?                                ");
        paramList.add(schoolId);
        paramList.add(2);
        paramList.add(1);
        paramList.add(1);

        for (String lessonId : lists) {
            sb1.append("   and t1.id != ?                               ");
            paramList.add(lessonId);
        }

        if (StringUtils.isNotBlank(catalogId1)) {
            sb1.append(" and t1.catalog_id1 = ?                         ");
            paramList.add(catalogId1);
        }
        if (StringUtils.isNotBlank(catalogId2)) {
            sb1.append(" and t1.catalog_id2 = ?                         ");
            paramList.add(catalogId2);
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb1.append(" and t1.name like ?                             ");
            paramList.add(keyword);
        }
        Integer allPage = jdbcTemplate.queryForObject(sb1.toString(), paramList.toArray(), Integer.class);

        StringBuffer sb2 = new StringBuffer();
        sb2.append("  select                                            ");
        sb2.append("    t1.id,                                          ");
        sb2.append("    t1.name,                                        ");
        sb2.append("    t1.catalog_id1,                                 ");
        sb2.append("    t1.catalog_id2,                                 ");
        sb2.append("    t1.count,                                       ");
        sb2.append("    t1.author,                                      ");
        sb2.append("    t2.name as catalogName1,                        ");
        sb2.append("    t3.name as catalogName2                         ");
        sb2.append("  from yxy_lessons.yxy_lessons_school_lesson t1                 ");
        sb2.append("    left join yxy_lessons.yxy_lessons_school_catalog t2         ");
        sb2.append("      on (t1.catalog_id1 = t2.id)                   ");
        sb2.append("    left join yxy_lessons.yxy_lessons_school_catalog t3         ");
        sb2.append("      on (t1.catalog_id2 = t3.id)                   ");
        sb2.append("  where t1.school_id = ?                            ");
        sb2.append("      and t1.status = ?                             ");
        sb2.append("      and t1.is_visible = ?                         ");
        sb2.append("      and t1.vip = ?                                ");

        for (String lessonId : lists) {
            sb2.append(" and t1.id != ?                                 ");
        }
        if (StringUtils.isNotBlank(catalogId1)) {
            sb2.append(" and t1.catalog_id1 = ?                         ");
        }
        if (StringUtils.isNotBlank(catalogId2)) {
            sb2.append(" and t1.catalog_id2 = ?                         ");
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb2.append(" and t1.name like ?                             ");
        }
        sb2.append("     order by t1.last_modified_date desc                 ");
        sb2.append("     limit ?,?                                      ");
        paramList.add((pageNum - 1) * 10);
        paramList.add(10);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sb2.toString(), paramList.toArray());

        Map<String, Object> map = new HashMap<>();
        map.put("allPage", allPage);
        map.put("list", list);
        return map;
    }

    @Override
    public List<Map<String, Object>> authStudentIdList(String schoolId, String classId) {
        StringBuffer sb = new StringBuffer();
        sb.append("  select                                             ");
        sb.append("    user_id                                          ");
        sb.append("  from yxy_lessons.yxy_lessons_school_lesson_auth    ");
        sb.append("  where school_id = ?                                ");
        sb.append("      and class_id = ?                               ");
        return jdbcTemplate.queryForList(sb.toString(), new Object[] { schoolId, classId });
    }
}
